#include "database_manager.h"
#include <QDebug>
#include <QSqlError>
#include <QSqlQuery>

QSqlDatabase &DatabaseManager::getInstance()
{
    static QSqlDatabase db;
    return db;
}

bool DatabaseManager::initialize()
{
    QSqlDatabase &db = getInstance();
    if (!db.isOpen())
    {
        db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("panel.db");
        if (!db.open())
        {
            qDebug() << "Database error:" << db.lastError().text();
            return false;
        }
        // 初始表
        ddl();
    }
    return true;
}

void DatabaseManager::ddl()
{
    QSqlDatabase &db = getInstance();
    if (!db.isOpen())
    {
        qDebug() << "Database is not open";
        return;
    }

    QSqlQuery query(db);

    // 创建用户表
    query.exec("CREATE TABLE IF NOT EXISTS config(id INTEGER PRIMARY KEY, config_key TEXT UNIQUE, config_val TEXT)");

    if (query.lastError().isValid())
    {
        qDebug() << "DDL error:" << query.lastError().text();
    }

    // 创建场站表
    query.exec("CREATE TABLE IF NOT EXISTS station(id INTEGER PRIMARY KEY, station_name TEXT UNIQUE, host TEXT, port INTEGER, description TEXT, is_rtu INTEGER, is_active INTEGER, created_at INTEGER, updated_at INTEGER, UNIQUE(host, port))");

    if (query.lastError().isValid())
    {
        qDebug() << "DDL error:" << query.lastError().text();
    }

    // 创建告警规则表
    query.exec("CREATE TABLE IF NOT EXISTS alarm_rule(id INTEGER PRIMARY KEY, station_id INTEGER, rule_name TEXT, interval_secs INTEGER, threshold_value REAL, threshold_unit INTEGER, metric_type INTEGER, alarm_level INTEGER, created_at INTEGER, updated_at INTEGER, UNIQUE(station_id, interval_secs, metric_type))");

    if (query.lastError().isValid())
    {
        qDebug() << "DDL error:" << query.lastError().text();
    }

    // 点表地址配置表
    query.exec("CREATE TABLE IF NOT EXISTS point_address_config(id INTEGER PRIMARY KEY, station_id INTEGER UNIQUE, yx_addr_start INTEGER, yx_addr_num INTEGER, yc_addr_start INTEGER, yc_addr_num INTEGER, dd_addr_start INTEGER, dd_addr_num INTEGER, yk_addr_start INTEGER, yk_addr_num INTEGER, yt_addr_start INTEGER, yt_addr_num INTEGER, soe_addr_start INTEGER, soe_addr_num INTEGER)");

    if (query.lastError().isValid())
    {
        qDebug() << "DDL error:" << query.lastError().text();
    }

    // 点表配置表
    query.exec("CREATE TABLE IF NOT EXISTS point_setting(id INTEGER PRIMARY KEY, station_id INTEGER, point_type INTEGER, point_desc TEXT, point_addr INTEGER, direction INTEGER, UNIQUE(station_id, point_addr))");

    if (query.lastError().isValid())
    {
        qDebug() << "DDL error:" << query.lastError().text();
    }

    // 控制-采集点表关联
    query.exec("CREATE TABLE IF NOT EXISTS point_relation(id INTEGER PRIMARY KEY, station_id INTEGER, point_ctrl INTEGER, point_grab INTEGER, UNIQUE(station_id, point_ctrl))");

    if (query.lastError().isValid())
    {
        qDebug() << "DDL error:" << query.lastError().text();
    }
}

void DatabaseManager::close()
{
    QSqlDatabase &db = getInstance();
    if (db.isOpen())
    {
        db.close();
    }
}

QVector<PointInfo> DatabaseManager::queryPoints(int64_t station_id, int point_type)
{
    QVector<PointInfo> points;
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("SELECT point_addr, point_desc FROM point_setting WHERE station_id = ? AND point_type = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(point_type);

    if (!query.exec())
    {
        qDebug() << "查询点表信息失败:" << query.lastError().text();
        return points;
    }

    while (query.next())
    {
        PointInfo point;
        point.point_id = query.value(0).toLongLong();
        point.point_desc = query.value(1).toString();
        points.append(point);
    }

    return points;
}

int64_t DatabaseManager::queryPointGrab(int64_t station_id, int64_t point_ctrl)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("SELECT point_grab FROM point_relation WHERE point_ctrl = ? AND station_id = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(point_ctrl));
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));

    if (!query.exec())
    {
        qDebug() << "查询点表关联关系失败:" << query.lastError().text();
        return -1;
    }

    if (query.next())
    {
        return query.value(0).toLongLong();
    }

    return -1;
}

bool DatabaseManager::deletePoints(int64_t station_id, int64_t point_addr)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("DELETE FROM point_setting WHERE station_id = ? AND point_addr = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(QVariant::fromValue<qlonglong>(point_addr));

    if (!query.exec())
    {
        qDebug() << "删除点表信息失败:" << query.lastError().text();
        return false;
    }

    return true;
}

int DatabaseManager::insertPoint(int64_t station_id, int point_type, int point_addr, QString point_desc, int direction)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("INSERT INTO point_setting (station_id, point_type, point_addr, point_desc, direction) VALUES (?, ?, ?, ?, ?)");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(point_type);
    query.addBindValue(point_addr);
    query.addBindValue(point_desc);
    query.addBindValue(direction);

    if (!query.exec())
    {
        qDebug() << "插入点表信息失败:" << query.lastError().text();
        return query.lastError().number();
    }
    return 0;
}

int DatabaseManager::insertPointRelation(int64_t station_id, int64_t point_ctrl, int64_t point_grab)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("INSERT INTO point_relation (station_id, point_ctrl, point_grab) VALUES (?, ?, ?)");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(QVariant::fromValue<qlonglong>(point_ctrl));
    query.addBindValue(QVariant::fromValue<qlonglong>(point_grab));

    if (!query.exec())
    {
        qDebug() << "插入点表关联关系失败:" << query.lastError().text();
        return query.lastError().number();
    }
    return 0;
}

bool DatabaseManager::deletePointRelation(int64_t station_id, int64_t point_ctrl)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("DELETE FROM point_relation WHERE station_id = ? AND point_ctrl = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(QVariant::fromValue<qlonglong>(point_ctrl));

    if (!query.exec())
    {
        qDebug() << "删除点表关联关系失败:" << query.lastError().text();
        return false;
    }
    return true;
}

bool DatabaseManager::reverseDeletePointRelation(int64_t station_id, int64_t point_grab)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("DELETE FROM point_relation WHERE station_id = ? AND point_grab = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(QVariant::fromValue<qlonglong>(point_grab));

    if (!query.exec())
    {
        qDebug() << "反向删除点表关联关系失败:" << query.lastError().text();
        return false;
    }
    return true;
}

QVector<PointInfo> DatabaseManager::queryAllPoints()
{
    QVector<PointInfo> points;
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.exec("SELECT point_type, point_desc, point_addr, direction, station_id FROM point_setting");

    while (query.next())
    {
        PointInfo point;
        point.point_type = static_cast<PointInfo::Type>(query.value(0).toInt());
        point.point_desc = query.value(1).toString();
        point.point_id = query.value(2).toLongLong();
        point.direction = query.value(3).toInt();
        point.station_id = query.value(4).toLongLong();
        points.append(point);
    }

    return points;
}

QVector<PointInfo> DatabaseManager::queryAllPointRelation()
{
    QVector<PointInfo> points;
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.exec("SELECT station_id, point_ctrl, point_grab FROM point_relation");

    while (query.next())
    {
        PointInfo point;
        point.station_id = query.value(0).toLongLong();
        point.point_id = query.value(1).toLongLong();
        point.relation_point_address = query.value(2).toLongLong();
        points.append(point);
    }

    return points;
}

bool DatabaseManager::savePointAddressConfig(int64_t station_id,
                                             int64_t yx_addr_start,
                                             int64_t yc_addr_start,
                                             int64_t power_addr_start,
                                             int64_t yk_addr_start,
                                             int64_t yt_addr_start,
                                             int64_t soe_addr_start)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    // 检查是否存在记录
    query.prepare("SELECT * FROM point_address_config WHERE station_id = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));

    if (!query.exec())
    {
        qDebug() << "查询点表地址配置失败:" << query.lastError().text();
        return false;
    }

    if (!query.next())
    {
        // 不存在记录，插入新记录
        query.prepare("INSERT INTO point_address_config ("
                      "station_id, yx_addr_start, yx_addr_num, yc_addr_start, yc_addr_num, dd_addr_start, dd_addr_num, yk_addr_start, yk_addr_num, yt_addr_start, yt_addr_num, soe_addr_start, soe_addr_num) "
                      "VALUES (?, ?, 0, ?, 0, ?, 0, ?, 0, ?, 0, ?, 0)");
        query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
        query.addBindValue(QVariant::fromValue<qlonglong>(yx_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(yc_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(power_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(yk_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(yt_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(soe_addr_start));

        if (!query.exec())
        {
            qDebug() << "插入点表地址配置失败:" << query.lastError().text();
            return false;
        }
    }
    else
    {
        query.prepare("UPDATE point_address_config SET yx_addr_start = ?, yc_addr_start = ?, dd_addr_start = ?, yk_addr_start = ?, yt_addr_start = ?, soe_addr_start = ? WHERE station_id = ?");

        query.addBindValue(QVariant::fromValue<qlonglong>(yx_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(yc_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(power_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(yk_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(yt_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(soe_addr_start));
        query.addBindValue(QVariant::fromValue<qlonglong>(station_id));

        if (!query.exec())
        {
            qDebug() << "更新点表地址配置失败:" << query.lastError().text();
            return false;
        }
    }

    return true;
}

bool DatabaseManager::initPointAddressConfig(int64_t station_id)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    // 检查是否存在记录
    query.prepare("SELECT * FROM point_address_config WHERE station_id = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));

    if (!query.exec())
    {
        qDebug() << "查询点表地址配置失败:" << query.lastError().text();
        return false;
    }

    if (query.next())
    {
        qDebug() << "点表地址配置已存在:" << query.value(0).toLongLong();
        return true;
    }

    // 不存在记录，插入新记录
    query.exec("INSERT INTO point_address_config (station_id, yx_addr_start, yx_addr_num, yc_addr_start, yc_addr_num, dd_addr_start, dd_addr_num, yk_addr_start, yk_addr_num, yt_addr_start, yt_addr_num, soe_addr_start, soe_addr_num) "
               "VALUES (?, ?, 0, ?, 0, ?, 0, ?, 0, ?, 0, ?, 0)");

    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(QVariant::fromValue<qlonglong>(1));
    query.addBindValue(QVariant::fromValue<qlonglong>(0x4001));
    query.addBindValue(QVariant::fromValue<qlonglong>(0x6401));
    query.addBindValue(QVariant::fromValue<qlonglong>(0x6001));
    query.addBindValue(QVariant::fromValue<qlonglong>(0x6201));
    query.addBindValue(QVariant::fromValue<qlonglong>(0x1));

    if (!query.exec())
    {
        qDebug() << "初始化点表地址配置失败:" << query.lastError().text();
        return false;
    }

    return true;
}

bool DatabaseManager::deletePointAddressConfig(int64_t station_id)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("DELETE FROM point_address_config WHERE station_id = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));

    if (!query.exec())
    {
        qDebug() << "删除点表地址配置失败:" << query.lastError().text();
        return false;
    }
}

StateInfo::PointSetting DatabaseManager::queryPointAddressConfig(int64_t station_id, bool &is_ok)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("SELECT yx_addr_start, yc_addr_start, dd_addr_start, yk_addr_start, yt_addr_start, soe_addr_start FROM point_address_config WHERE station_id = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));

    if (!query.exec())
    {
        qDebug() << "查询点表地址配置失败:" << query.lastError().text();
        is_ok = false;
        return StateInfo::PointSetting();
    }

    if (query.next())
    {
        is_ok = true;
        StateInfo::PointSetting point_setting;
        point_setting.yx_addr_start = query.value(0).toLongLong();
        point_setting.yc_addr_start = query.value(1).toLongLong();
        point_setting.dd_addr_start = query.value(2).toLongLong();
        point_setting.yk_addr_start = query.value(3).toLongLong();
        point_setting.yt_addr_start = query.value(4).toLongLong();
        point_setting.soe_addr_start = query.value(5).toLongLong();
        return point_setting;
    }

    return StateInfo::PointSetting();
}

QVector<AlarmRule> DatabaseManager::queryAllAlarmRules()
{
    QVector<AlarmRule> alarm_rules;
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);
    
    query.prepare("SELECT id, station_id, rule_name, interval_secs, threshold_value, threshold_unit, metric_type, alarm_level FROM alarm_rule order by interval_secs, metric_type");

    if (!query.exec())
    {
        qDebug() << "查询告警规则失败:" << query.lastError().text();
        return alarm_rules;
    }
    while (query.next())
    {
        int id = query.value(0).toInt();
        int stateId = query.value(1).toInt();
        QString ruleName = query.value(2).toString();
        int intervalSecs = query.value(3).toInt();
        double thresholdValue = query.value(4).toDouble();
        ThresholdUnitEnum thresholdUnit = static_cast<ThresholdUnitEnum>(query.value(5).toInt());
        MetricTypeEnum metricType = static_cast<MetricTypeEnum>(query.value(6).toInt());
        AlarmLevelEnum alarmLevel = static_cast<AlarmLevelEnum>(query.value(7).toInt());

        AlarmRule rule(metricType, alarmLevel, thresholdUnit, stateId);
        rule.setId(id);
        rule.setIntervalSecs(intervalSecs);
        rule.setThresholdValue(thresholdValue);
        rule.setEnabled(true);

        // 将规则添加到对应变电站的规则列表中
        alarm_rules.append(rule);
    }

    return alarm_rules;
}

QVector<AlarmRule> DatabaseManager::queryAlarmRules(int64_t station_id)
{
    QVector<AlarmRule> alarm_rules;
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);
    
    query.prepare("SELECT id, station_id, rule_name, interval_secs, threshold_value, threshold_unit, metric_type, alarm_level FROM alarm_rule WHERE station_id = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));

    if (!query.exec())
    {
        qDebug() << "查询告警规则失败:" << query.lastError().text();
    }
    while (query.next())
    {
        int id = query.value(0).toInt();
        int stateId = query.value(1).toInt();
        QString ruleName = query.value(2).toString();
        int intervalSecs = query.value(3).toInt();
        double thresholdValue = query.value(4).toDouble();
        ThresholdUnitEnum thresholdUnit = static_cast<ThresholdUnitEnum>(query.value(5).toInt());
        MetricTypeEnum metricType = static_cast<MetricTypeEnum>(query.value(6).toInt());
        AlarmLevelEnum alarmLevel = static_cast<AlarmLevelEnum>(query.value(7).toInt());

        AlarmRule rule(metricType, alarmLevel, thresholdUnit, stateId);
        rule.setId(id);
        rule.setIntervalSecs(intervalSecs);
        rule.setThresholdValue(thresholdValue);
        rule.setEnabled(true);
        alarm_rules.append(rule);
    }
    return alarm_rules;
}

int DatabaseManager::insertAlarmRule(AlarmRule alarm_rule)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("INSERT INTO alarm_rule (station_id, rule_name, interval_secs, threshold_value, threshold_unit, metric_type, alarm_level) VALUES (?, ?, ?, ?, ?, ?, ?)");
    query.addBindValue(QVariant::fromValue<qlonglong>(alarm_rule.stateId()));
    query.addBindValue(AlarmRule::getMetricTypeText(alarm_rule.metricType()));
    query.addBindValue(alarm_rule.intervalSecs());
    query.addBindValue(alarm_rule.thresholdValue());
    query.addBindValue(static_cast<int>(alarm_rule.thresholdUnit()));
    query.addBindValue(static_cast<int>(alarm_rule.metricType()));
    query.addBindValue(static_cast<int>(alarm_rule.alarmLevel()));

    if (!query.exec())
    {
        qDebug() << "插入告警规则失败:" << query.lastError().text();
        return query.lastError().number();
    }
    return 0;
}

int DatabaseManager::deleteAlarmRule(int id)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("DELETE FROM alarm_rule WHERE id = ?");
    query.addBindValue(id);

    if (!query.exec())
    {
        qDebug() << "删除告警规则失败:" << query.lastError().text();
        return query.lastError().number();
    }
    return 0;
}

QString DatabaseManager::queryPointDesc(int64_t station_id, int64_t point_addr)
{
    QSqlDatabase &db = getInstance();
    QSqlQuery query(db);

    query.prepare("SELECT point_desc FROM point_setting WHERE station_id = ? AND point_addr = ?");
    query.addBindValue(QVariant::fromValue<qlonglong>(station_id));
    query.addBindValue(QVariant::fromValue<qlonglong>(point_addr));

    if (!query.exec())
    {
        qDebug() << "查询点表描述失败:" << query.lastError().text();
        return "";
    }

    if (query.next())
    {
        return query.value(0).toString();
    }

    return "";
}